package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import modele.EtatInscription;

public class EtatInscriptionDAO implements IDAO<EtatInscription> {
	static Connection connection = null;
	static Statement stmt = null;
	static final String SQL_INSERER_ETAT_INSCRIPTION = "INSERT INTO `agriotes`.`etat_inscription` (`id_etat_inscription`, `libelle`) VALUES (NULL, ?);";
	static final String SQL_MODIFIER_ETAT_INSCRIPTION = "UPDATE `agriotes`.`etat_inscription` SET  `libelle` = ? WHERE  `etat_inscription`.`id_etat_inscription` = ?";
	static final String SQL_SUPPRIMER_PAR_ID = "DELETE FROM `agriotes`.`etat_inscription` WHERE `etat_inscription`.`id_etat_inscription` = ?";
	static final String SQL_CHERCHER_PAR_ID = "SELECT * FROM `agriotes`.`etat_inscription` WHERE `etat_inscription`.`id_etat_inscription` = ?";
	static final String SQL_LISTER_ETAT_INSCRIPTION = "SELECT * FROM `agriotes`.`etat_inscription`ORDER BY `id_etat_inscription` ASC";
	private static final String ID_ETAT_INSCRIPTION = "id_etat_inscription";
	private static final String LIBELLE = "libelle";

	public EtatInscriptionDAO() throws SQLException {
		connection = FabriqueDAO.getConnection();
		stmt = connection.createStatement();
	}

	/**
	 * méthode pour créer un nouveau EtatInscription dans la base de données
	 * 
	 * @param etat
	 */

	@Override
	public void inserer(EtatInscription etat) throws SQLException {

		PreparedStatement pt = connection.prepareStatement(
				SQL_INSERER_ETAT_INSCRIPTION, Statement.RETURN_GENERATED_KEYS);
		pt.setString(1, etat.getLibelle());
		pt.executeUpdate();
		ResultSet rs = pt.getGeneratedKeys();

		if (rs.next()) {
			// set id to this object
			etat.setId(rs.getInt(1));
		}
	}

	/**
	 * méthode pour chercher les informations d'un EtatInscription selon son id
	 * 
	 * @param id
	 * @return EtatInscription
	 */

	@Override
	public EtatInscription lireParId(int id) throws SQLException {
		EtatInscription etat = null;
		PreparedStatement pt = connection.prepareStatement(SQL_CHERCHER_PAR_ID);
		pt.setInt(1, id);
		ResultSet rs = pt.executeQuery();

		if (rs.next()) {

			etat = new EtatInscription();
			etat.setId(rs.getInt(ID_ETAT_INSCRIPTION));
			etat.setLibelle(rs.getString(LIBELLE));
		}
		return etat;
	}

	/**
	 * méthode pour modifier un EtatInscription de la base de données
	 * 
	 * @param EtatInscription
	 */

	@Override
	public void modifier(EtatInscription etat) throws SQLException {

		PreparedStatement pt = connection
				.prepareStatement(SQL_MODIFIER_ETAT_INSCRIPTION);

		pt.setString(1, etat.getLibelle());
		pt.setInt(2, etat.getId());
		pt.executeUpdate();
	}

	/**
	 * méthode pour effacer un EtatInscription de la base de données selon son
	 * id
	 * 
	 * @param id
	 */

	@Override
	public void supprimer(int id) throws SQLException {

		PreparedStatement pt = connection
				.prepareStatement(SQL_SUPPRIMER_PAR_ID);
		pt.setInt(1, id);
		pt.executeUpdate();
	}

	/**
	 * méthode pour lister toutes les EtatInscription de la table
	 * EtatInscription
	 * 
	 * @return tableauEtatInscription
	 */

	@Override
	public List<EtatInscription> lister() throws SQLException {

		ArrayList<EtatInscription> tableauEtatInscription = new ArrayList<>();
		PreparedStatement pt = connection
				.prepareStatement(SQL_LISTER_ETAT_INSCRIPTION);
		ResultSet rs = pt.executeQuery();

		while (rs.next()) {

			int id_etat_inscription = rs.getInt(ID_ETAT_INSCRIPTION);
			String libelle = rs.getString(LIBELLE);

			EtatInscription etat = new EtatInscription(id_etat_inscription,
					libelle);

			tableauEtatInscription.add(etat);
		}
		return tableauEtatInscription;
	}
}
